--
-- PostgreSQL database dump
--

-- Started on 2011-06-23 20:47:49 BRT

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 394 (class 2612 OID 17618)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

SET search_path = public, pg_catalog;

--
-- TOC entry 331 (class 1247 OID 17614)
-- Dependencies: 3
-- Name: dm_cpf; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_cpf AS character(14) NOT NULL;


ALTER DOMAIN public.dm_cpf OWNER TO postgres;

--
-- TOC entry 329 (class 1247 OID 17612)
-- Dependencies: 3
-- Name: dm_local; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_local AS character varying(80);


ALTER DOMAIN public.dm_local OWNER TO postgres;

--
-- TOC entry 326 (class 1247 OID 17609)
-- Dependencies: 3
-- Name: dm_nome; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_nome AS character varying(100) NOT NULL;


ALTER DOMAIN public.dm_nome OWNER TO postgres;

--
-- TOC entry 327 (class 1247 OID 17610)
-- Dependencies: 3
-- Name: dm_nome2; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_nome2 AS character varying(100);


ALTER DOMAIN public.dm_nome2 OWNER TO postgres;

--
-- TOC entry 332 (class 1247 OID 17615)
-- Dependencies: 3
-- Name: dm_rg; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_rg AS character(12) NOT NULL;


ALTER DOMAIN public.dm_rg OWNER TO postgres;

--
-- TOC entry 328 (class 1247 OID 17611)
-- Dependencies: 3
-- Name: dm_telefone; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_telefone AS character varying(14);


ALTER DOMAIN public.dm_telefone OWNER TO postgres;

--
-- TOC entry 330 (class 1247 OID 17613)
-- Dependencies: 3
-- Name: dm_uf; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_uf AS character(2) NOT NULL;


ALTER DOMAIN public.dm_uf OWNER TO postgres;

--
-- TOC entry 374 (class 1247 OID 18619)
-- Dependencies: 3 1602
-- Name: tp_profissional_profissao; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE tp_profissional_profissao AS (
	codprofissional integer,
	nome dm_nome,
	codprofissao integer,
	profissao dm_nome,
	nivel smallint,
	alocado boolean
);


ALTER TYPE public.tp_profissional_profissao OWNER TO postgres;

--
-- TOC entry 380 (class 1247 OID 18659)
-- Dependencies: 3 1605
-- Name: tp_solicitacoes; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE tp_solicitacoes AS (
	codsolicitacao bigint,
	codcliente bigint,
	cliente dm_nome,
	quantidade integer,
	codprofissao integer,
	profissao dm_nome,
	datasolicitacao date,
	dataatendimento date
);


ALTER TYPE public.tp_solicitacoes OWNER TO postgres;

--
-- TOC entry 38 (class 1255 OID 19233)
-- Dependencies: 394 3
-- Name: atualizaalocacaoprofissional(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION atualizaalocacaoprofissional() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
	declare
	begin
		if new.aceito = true then
			update profissional set alocado=true where codprofissional = new.profissionalcodprofissional;
		end if;
		return new;
	end;
$$;


ALTER FUNCTION public.atualizaalocacaoprofissional() OWNER TO postgres;

--
-- TOC entry 21 (class 1255 OID 18606)
-- Dependencies: 394 3
-- Name: atualizanivelprofissional(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION atualizanivelprofissional() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
	declare
		profissao int;
	begin
		if new.nivel <> old.nivel then
			select into profissao profissaocodprofissao
			from solicitacao
			where codsolicitacao = new.solicitacaocodsolicitacao;
	
			update especializacao set nivel = (select avg(new.nivel) 
								from clientesprofissional cp, solicitacao s
								where s.profissaocodprofissao = profissao and
								cp.profissionalcodprofissional = new.profissionalcodprofissional and
								cp.aceito = 'true')
			where especializacao.profissionalcodprofissional = new.profissionalcodprofissional and
				especializacao.profissaocodprofissao = profissao;
		end if;
		return new;
	end;
$$;


ALTER FUNCTION public.atualizanivelprofissional() OWNER TO postgres;

--
-- TOC entry 40 (class 1255 OID 18946)
-- Dependencies: 394 3
-- Name: fn_atualiza_area(integer, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_atualiza_area(integer, novonome text) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
DECLARE
	var_sistema integer; -- variavel q recebe valores do sistema.
BEGIN
	-- atualizando
	update area set descricao = novoNome where codarea = $1;
	
	GET DIAGNOSTICS var_sistema := ROW_COUNT;
	RETURN var_sistema > 0;
END;
$_$;


ALTER FUNCTION public.fn_atualiza_area(integer, novonome text) OWNER TO postgres;

--
-- TOC entry 23 (class 1255 OID 18051)
-- Dependencies: 3
-- Name: fn_atualizanivel(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_atualizanivel(integer, integer, integer) RETURNS smallint
    LANGUAGE sql
    AS $_$
	-- atualizando
	UPDATE Especializacao set nivel = nivel + $3 
		WHERE ProfissionalCodProfissional = $1 AND profissaoCodProfissao = $2;
	
	-- retornando nivel
	select nivel from vw_profissional_profissao
		where codprofissional = 1 and codprofissao = 1
		limit 1
$_$;


ALTER FUNCTION public.fn_atualizanivel(integer, integer, integer) OWNER TO postgres;

--
-- TOC entry 41 (class 1255 OID 18947)
-- Dependencies: 3
-- Name: fn_existe_profissional_cpf(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_existe_profissional_cpf(text) RETURNS boolean
    LANGUAGE sql
    AS $_$
	select count(cpf) > 0 from profissional where cpf = $1
$_$;


ALTER FUNCTION public.fn_existe_profissional_cpf(text) OWNER TO postgres;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 1586 (class 1259 OID 17886)
-- Dependencies: 1907 1908 1909 1910 1911 1912 1913 329 329 329 328 328 332 327 326 331 3 330
-- Name: profissional; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE profissional (
    codprofissional integer NOT NULL,
    cpf dm_cpf,
    nome dm_nome,
    fantasia dm_nome2,
    sexo character(1) NOT NULL,
    fonefixo dm_telefone,
    fonecel dm_telefone,
    endereco dm_local,
    bairro dm_local,
    cidade dm_local,
    uf dm_uf,
    rg_num dm_rg,
    rg_org_exp character(6) NOT NULL,
    rg_data_exp date NOT NULL,
    carteiraprofissional character varying(14) NOT NULL,
    cnh_registro character varying(12),
    cnh_cat character varying(10),
    cnh_vencto date,
    datanascimento date NOT NULL,
    alocado boolean NOT NULL,
    CONSTRAINT valida_cnh CHECK ((cnh_vencto < ('now'::text)::date)),
    CONSTRAINT valida_cpf CHECK (((length((cpf)::bpchar) = 14) AND ((uf)::bpchar <> ' '::bpchar))),
    CONSTRAINT valida_expedicao CHECK ((rg_data_exp < ('now'::text)::date)),
    CONSTRAINT valida_fantasia CHECK (((fantasia)::text <> (nome)::text)),
    CONSTRAINT valida_fone CHECK (((fonefixo)::text <> (fonecel)::text)),
    CONSTRAINT valida_rg CHECK (((rg_num)::bpchar <> ' '::bpchar)),
    CONSTRAINT valida_uf CHECK (((length((uf)::text) = 2) AND ((uf)::bpchar <> ' '::bpchar)))
);


ALTER TABLE public.profissional OWNER TO postgres;

--
-- TOC entry 28 (class 1255 OID 18694)
-- Dependencies: 342 3
-- Name: fn_filtra_profissionais(integer[], integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_filtra_profissionais(integer[], integer) RETURNS SETOF profissional
    LANGUAGE sql
    AS $_$
	select * from vw_profissionais_disponiveis v
		where 	v.codProfissional = ANY($1)
				and v.codProfissional IN (SELECT codProfissional FROM vw_profissional_profissao where CodProfissao = $2)
$_$;


ALTER FUNCTION public.fn_filtra_profissionais(integer[], integer) OWNER TO postgres;

--
-- TOC entry 1582 (class 1259 OID 17864)
-- Dependencies: 3 326
-- Name: area; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE area (
    codarea integer NOT NULL,
    descricao dm_nome
);


ALTER TABLE public.area OWNER TO postgres;

--
-- TOC entry 27 (class 1255 OID 18581)
-- Dependencies: 334 3
-- Name: fn_filtrar_area(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_filtrar_area(text) RETURNS SETOF area
    LANGUAGE sql
    AS $_$
	SELECT * FROM Area WHERE LOWER(descricao) LIKE fn_NoCase($1)
$_$;


ALTER FUNCTION public.fn_filtrar_area(text) OWNER TO postgres;

--
-- TOC entry 1587 (class 1259 OID 17897)
-- Dependencies: 1914 3
-- Name: especializacao; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE especializacao (
    profissionalcodprofissional integer NOT NULL,
    profissaocodprofissao integer NOT NULL,
    dataespecializacao date NOT NULL,
    nivel smallint NOT NULL,
    CONSTRAINT valida_nivel2 CHECK (((nivel <= 5) AND (nivel >= 1)))
);


ALTER TABLE public.especializacao OWNER TO postgres;

--
-- TOC entry 1584 (class 1259 OID 17875)
-- Dependencies: 3 326
-- Name: profissao; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE profissao (
    codprofissao integer NOT NULL,
    descricao dm_nome NOT NULL,
    areacodarea integer NOT NULL
);


ALTER TABLE public.profissao OWNER TO postgres;

--
-- TOC entry 1594 (class 1259 OID 17968)
-- Dependencies: 1699 326 326 3
-- Name: vw_profissional_profissao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_profissional_profissao AS
    SELECT profissional.codprofissional, profissional.nome, profissao.codprofissao, profissao.descricao AS profissao, especializacao.nivel, profissional.alocado FROM ((especializacao JOIN profissional ON ((profissional.codprofissional = especializacao.profissionalcodprofissional))) JOIN profissao ON ((profissao.codprofissao = especializacao.profissaocodprofissao)));


ALTER TABLE public.vw_profissional_profissao OWNER TO postgres;

--
-- TOC entry 1599 (class 1259 OID 18003)
-- Dependencies: 1704 326 3 326
-- Name: vwr_profissionais_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vwr_profissionais_solicitacao AS
    SELECT vw_profissional_profissao.profissao, vw_profissional_profissao.nome, vw_profissional_profissao.nivel FROM vw_profissional_profissao ORDER BY vw_profissional_profissao.codprofissao, vw_profissional_profissao.nivel DESC, vw_profissional_profissao.nome;


ALTER TABLE public.vwr_profissionais_solicitacao OWNER TO postgres;

--
-- TOC entry 26 (class 1255 OID 18538)
-- Dependencies: 3 368
-- Name: fn_filtrar_area_quantidade(text, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_filtrar_area_quantidade(text, text) RETURNS SETOF vwr_profissionais_solicitacao
    LANGUAGE sql
    AS $_$
	select * from vwr_profissionais_solicitacao
		where lower(profissao) like fn_NoCase($1)
		and lower(nome) like fn_NoCase($2)

$_$;


ALTER FUNCTION public.fn_filtrar_area_quantidade(text, text) OWNER TO postgres;

--
-- TOC entry 37 (class 1255 OID 18582)
-- Dependencies: 3 338
-- Name: fn_filtrar_profissao(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_filtrar_profissao(integer) RETURNS SETOF profissao
    LANGUAGE sql
    AS $_$
	select * from Profissao where AreaCodArea = $1
$_$;


ALTER FUNCTION public.fn_filtrar_profissao(integer) OWNER TO postgres;

--
-- TOC entry 25 (class 1255 OID 18056)
-- Dependencies: 3
-- Name: fn_get_area_profissao(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_get_area_profissao(text) RETURNS integer
    LANGUAGE sql
    AS $_$
	select AreaCodArea from Profissao where Profissao.Descricao = $1 limit 1;
$_$;


ALTER FUNCTION public.fn_get_area_profissao(text) OWNER TO postgres;

--
-- TOC entry 20 (class 1255 OID 18047)
-- Dependencies: 3
-- Name: fn_getclienteid(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_getclienteid(text) RETURNS bigint
    LANGUAGE sql
    AS $_$
	select CodCliente from Cliente where nomecli = $1;
$_$;


ALTER FUNCTION public.fn_getclienteid(text) OWNER TO postgres;

--
-- TOC entry 22 (class 1255 OID 18048)
-- Dependencies: 3
-- Name: fn_getprofissionalid(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_getprofissionalid(text) RETURNS integer
    LANGUAGE sql
    AS $_$
	select CodProfissional from Profissional where nome = $1;
$_$;


ALTER FUNCTION public.fn_getprofissionalid(text) OWNER TO postgres;

--
-- TOC entry 36 (class 1255 OID 18057)
-- Dependencies: 394 3
-- Name: fn_inserir_especializacao(text, text, date, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_inserir_especializacao(novo_profissional text, nova_profissao text, data_especializacao date, novo_nivel integer) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
DECLARE
	cod_profissional integer;
	cod_profissao integer;
	var_sistema integer; -- variavel q recebe valores do sistema.
BEGIN
	-- verifica se tem a area da profissao
	select into cod_profissao CodProfissao from Profissao where Profissao.Descricao = nova_profissao;
	
	if FOUND then
		-- verifica se tem o profissional
		cod_profissional := fn_getProfissionalId(novo_profissional);
		--raise notice 'quanto deu o found %, %', FOUND, cod_profissional;
		--raise notice 'FOUND and % = %', (cod_profissional), (FOUND and cod_profissional>0);
		if (FOUND and cod_profissional>0) then
			--raise notice 'INSERT INTO Especializacao (ProfissionalCodProfissional, ProfissaoCodProfissao, DataEspecializacao, Nivel) VALUES (%,%,%,%);', cod_profissional, cod_profissao, data_especializacao, novo_nivel;
			INSERT INTO Especializacao
				(ProfissionalCodProfissional, ProfissaoCodProfissao, DataEspecializacao, Nivel) 
				VALUES (cod_profissional, cod_profissao, data_especializacao, novo_nivel);	
			GET DIAGNOSTICS var_sistema := ROW_COUNT;
			return var_sistema > 0;
		end if;
	end if;
	return false;
END;
$$;


ALTER FUNCTION public.fn_inserir_especializacao(novo_profissional text, nova_profissao text, data_especializacao date, novo_nivel integer) OWNER TO postgres;

--
-- TOC entry 24 (class 1255 OID 18054)
-- Dependencies: 3 394
-- Name: fn_inserir_profissao(text, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_inserir_profissao(nova_area text, nova_profissao text) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
DECLARE
	cod_area integer;
	var_sistema integer; -- variavel q recebe valores do sistema.
BEGIN
	-- verificar se existe area
	select into cod_area * from area where area.Descricao = nova_area;

	if FOUND then
		-- inserir profissao na area
		INSERT INTO Profissao(Descricao, AreaCodArea) VALUES (nova_profissao, cod_area);
	else
		-- criar area e inserir (recursivo)
		INSERT INTO Area(Descricao) VALUES (nova_area);
		return fn_inserir_profissao(nova_area, nova_profissao);
	end if;
	GET DIAGNOSTICS var_sistema := ROW_COUNT;
	
	return var_sistema > 0;
END;
$$;


ALTER FUNCTION public.fn_inserir_profissao(nova_area text, nova_profissao text) OWNER TO postgres;

--
-- TOC entry 39 (class 1255 OID 18697)
-- Dependencies: 3 394
-- Name: fn_inserir_solicitacao(integer, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_inserir_solicitacao(codsolicitacao integer, indicacoes integer[]) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
	row_solicitacao record; -- aqui eh para saber se pode contratar.  (qntde = indicacoes.length, DataAtendimento < hj ou nao nulo
	tempProf record; -- iterador
	
	qtdeProf integer; -- quantidade de profissinais disponiveis para alocar (baseado na array indicacoes)
	var_sistema integer; -- variavel q recebe valores do sistema.
	
BEGIN
	
	select into row_solicitacao * from Solicitacao s where s.CodSolicitacao = codSolicitacao;
	if FOUND then
		RAISE NOTICE 'row_solicitacao = %, %, %, %, %', row_solicitacao.ClienteCodCliente, row_solicitacao.ProfissaoCodProfissao, row_solicitacao.Qtde, row_solicitacao.DataSolicitacao, row_solicitacao.DataAtendimento;
		
		
		-- ja foi atendida?
		if (row_solicitacao.DataAtendimento is null) then
			-- nao, entao tenta contratar os caras.

			-- vendo quantas pessoas podem ser contradadas..
			select into qtdeProf COUNT(*) from fn_filtra_profissionais(indicacoes, row_solicitacao.ProfissaoCodProfissao);

			-- o numero de pessoas disponiveis batem com o numero solicitado?
			if (qtdeProf = row_solicitacao.qtde) then
				-- sim, entao contrata!
				for tempProf in select codprofissional from fn_filtra_profissionais (indicacoes, row_solicitacao.ProfissaoCodProfissao) loop
					-- RAISE NOTICE 'INSERT INTO ClientesProfissional(SolicitacaoCodSolicitacao, ProfissionalCodProfissional, Aceito, Nivel) values (%,%,%,%)', row_solicitacao.codSolicitacao, tempProf.codProfissional, true, 1;
					INSERT INTO ClientesProfissional(SolicitacaoCodSolicitacao, ProfissionalCodProfissional, Aceito, Nivel) values (row_solicitacao.codSolicitacao, tempProf.codProfissional, true, 1) ;
				end loop;
				
				RAISE NOTICE 'Contratacao sucedida';
				return 1;
			else 
				-- nao, entao avisa quandos precisa de verdade
				RAISE NOTICE 'achou % mas precisa de %', qtdeProf, row_solicitacao.qtde;
				
				-- quantidade nao bate
				return -1;
			end if;
				
			
			RAISE NOTICE 'achou: %', qtdeProf;
		end if;
		-- sim, entao retorna false
		
		-- solicitacao ja atendida
		RETURN -2;
	end if;
	-- solicitacao nao encontrada.
	RETURN -3; 
	--INSERT INTO ClientesProfissional(SolicitacaoCodSolicitacao, ProfissionalCodProfissional, Aceito, Nivel) 
		--values();
	
END;
$$;


ALTER FUNCTION public.fn_inserir_solicitacao(codsolicitacao integer, indicacoes integer[]) OWNER TO postgres;

--
-- TOC entry 32 (class 1255 OID 18622)
-- Dependencies: 374 3
-- Name: fn_lista_especialista(integer, boolean); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_lista_especialista(integer, boolean) RETURNS SETOF tp_profissional_profissao
    LANGUAGE sql
    AS $_$
	select * from vw_profissional_profissao 
		where codprofissao = $1
			and alocado = $2
			order by nivel desc
$_$;


ALTER FUNCTION public.fn_lista_especialista(integer, boolean) OWNER TO postgres;

--
-- TOC entry 30 (class 1255 OID 18620)
-- Dependencies: 374 3
-- Name: fn_lista_especializacao(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_lista_especializacao(text) RETURNS SETOF tp_profissional_profissao
    LANGUAGE sql
    AS $_$
	select * from vw_profissional_profissao
		where nome = $1;
$_$;


ALTER FUNCTION public.fn_lista_especializacao(text) OWNER TO postgres;

--
-- TOC entry 31 (class 1255 OID 18621)
-- Dependencies: 3 374
-- Name: fn_lista_especializacao_avancado(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_lista_especializacao_avancado(text) RETURNS SETOF tp_profissional_profissao
    LANGUAGE sql
    AS $_$
	select * from vw_profissional_profissao
		where lower(nome) like lower('%' || $1 || '%');
$_$;


ALTER FUNCTION public.fn_lista_especializacao_avancado(text) OWNER TO postgres;

--
-- TOC entry 33 (class 1255 OID 18683)
-- Dependencies: 3 380
-- Name: fn_lista_solicitacoes(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_lista_solicitacoes(text) RETURNS SETOF tp_solicitacoes
    LANGUAGE sql
    AS $_$
	select * from vw_solicitacoes 
		where lower(cliente) like lower('%' || $1 || '%')
$_$;


ALTER FUNCTION public.fn_lista_solicitacoes(text) OWNER TO postgres;

--
-- TOC entry 34 (class 1255 OID 18684)
-- Dependencies: 3 380 394
-- Name: fn_lista_solicitacoes(integer, boolean); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_lista_solicitacoes(codcliente integer, atendida boolean) RETURNS SETOF tp_solicitacoes
    LANGUAGE plpgsql
    AS $$
DECLARE
BEGIN
	
	if atendida then
		return query
			select * from vw_solicitacoes t
				where DataAtendimento > DataSolicitacao
					and t.CodCliente = codCliente
				;
	else
		return  query
			select * from vw_solicitacoes t
				where DataAtendimento < DataSolicitacao 
					or DataAtendimento is null
					and t.CodCliente = codCliente
					;
	end if;
	
END;
$$;


ALTER FUNCTION public.fn_lista_solicitacoes(codcliente integer, atendida boolean) OWNER TO postgres;

--
-- TOC entry 19 (class 1255 OID 18537)
-- Dependencies: 3
-- Name: fn_nocase(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_nocase(text) RETURNS text
    LANGUAGE sql
    AS $_$
	select lower('%' || $1 || '%');
$_$;


ALTER FUNCTION public.fn_nocase(text) OWNER TO postgres;

--
-- TOC entry 35 (class 1255 OID 18685)
-- Dependencies: 3
-- Name: fn_quantidade_solicitacoes(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_quantidade_solicitacoes(text) RETURNS TABLE(codcliente bigint, cliente character varying, quantidade bigint)
    LANGUAGE sql
    AS $_$
	select codcliente, cliente, sum(quantidade) from vw_solicitacoes 
		--where lower(cliente) like lower('%' || $1 || '%')
		where cliente = $1
		group by codcliente,cliente
$_$;


ALTER FUNCTION public.fn_quantidade_solicitacoes(text) OWNER TO postgres;

--
-- TOC entry 42 (class 1255 OID 19231)
-- Dependencies: 3 394
-- Name: fn_verifica_nome(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_verifica_nome() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE 
BEGIN
	--RAISE NOTICE 'ta inserindo: %', new.descricao;
	PERFORM * from profissao where lower(descricao) = lower(new.descricao) and new.codProfissao <> codProfissao;
	if (FOUND) then
		rollback;
		return null;
	else 
		return new;
	end if;	
END;
$$;


ALTER FUNCTION public.fn_verifica_nome() OWNER TO postgres;

--
-- TOC entry 29 (class 1255 OID 18638)
-- Dependencies: 394 3
-- Name: gerasenhausuario(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION gerasenhausuario() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
	declare
		password varchar(10);
		sequencia char(62);
		letra char;
	begin

		if new.senha = '' then
			password='';
			select into letra substr(sequencia,(round(random()*62))::int,1);
			for i in 1..10 loop
				select INTO letra substr('aAbB0cCdD1eEfF2gGhH3iIjJ4kKlL5mMnN6oOpP7qQrRs8StTu9UwWx0XyYzZ1',(round(random()*62))::int,1);
				password:=password || letra;
			end loop;
			update users set senha=password where id = new.id;
		end if;
		return new;
	end;
$$;


ALTER FUNCTION public.gerasenhausuario() OWNER TO postgres;

--
-- TOC entry 1581 (class 1259 OID 17862)
-- Dependencies: 3 1582
-- Name: area_codarea_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE area_codarea_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    MINVALUE 0
    CACHE 1;


ALTER TABLE public.area_codarea_seq OWNER TO postgres;

--
-- TOC entry 1973 (class 0 OID 0)
-- Dependencies: 1581
-- Name: area_codarea_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE area_codarea_seq OWNED BY area.codarea;


--
-- TOC entry 1974 (class 0 OID 0)
-- Dependencies: 1581
-- Name: area_codarea_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('area_codarea_seq', 3, true);


--
-- TOC entry 1589 (class 1259 OID 17904)
-- Dependencies: 1916 1917 1918 329 327 329 329 329 330 328 328 328 326 329 3
-- Name: cliente; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cliente (
    codcliente bigint NOT NULL,
    nomecli dm_nome,
    fantasia dm_nome2,
    endereco dm_local,
    bairro dm_local,
    cidade dm_local,
    uf dm_uf,
    fonefixo dm_telefone,
    fonefax dm_telefone,
    fonecelular dm_telefone,
    email dm_local,
    homepage dm_local,
    tipo character(1) NOT NULL,
    datanasctoabertura date,
    cpfcnpj character varying(18),
    rgie character varying(18),
    CONSTRAINT valida_fantasia CHECK (((fantasia)::text <> (nomecli)::text)),
    CONSTRAINT valida_fone CHECK (((fonefixo)::text <> (fonecelular)::text)),
    CONSTRAINT valida_uf CHECK (((length((uf)::bpchar) = 2) AND ((uf)::bpchar <> ' '::bpchar)))
);


ALTER TABLE public.cliente OWNER TO postgres;

--
-- TOC entry 1588 (class 1259 OID 17902)
-- Dependencies: 3 1589
-- Name: cliente_codcliente_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE cliente_codcliente_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    MINVALUE 0
    CACHE 1;


ALTER TABLE public.cliente_codcliente_seq OWNER TO postgres;

--
-- TOC entry 1975 (class 0 OID 0)
-- Dependencies: 1588
-- Name: cliente_codcliente_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE cliente_codcliente_seq OWNED BY cliente.codcliente;


--
-- TOC entry 1976 (class 0 OID 0)
-- Dependencies: 1588
-- Name: cliente_codcliente_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('cliente_codcliente_seq', 2, true);


--
-- TOC entry 1592 (class 1259 OID 17923)
-- Dependencies: 1922 1923 3
-- Name: clientesprofissional; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE clientesprofissional (
    solicitacaocodsolicitacao bigint NOT NULL,
    profissionalcodprofissional integer DEFAULT 3 NOT NULL,
    aceito boolean,
    nivel smallint NOT NULL,
    CONSTRAINT valida_nivel CHECK (((nivel <= 5) AND (nivel >= 1)))
);


ALTER TABLE public.clientesprofissional OWNER TO postgres;

--
-- TOC entry 1583 (class 1259 OID 17873)
-- Dependencies: 3 1584
-- Name: profissao_codprofissao_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE profissao_codprofissao_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    MINVALUE 0
    CACHE 1;


ALTER TABLE public.profissao_codprofissao_seq OWNER TO postgres;

--
-- TOC entry 1977 (class 0 OID 0)
-- Dependencies: 1583
-- Name: profissao_codprofissao_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE profissao_codprofissao_seq OWNED BY profissao.codprofissao;


--
-- TOC entry 1978 (class 0 OID 0)
-- Dependencies: 1583
-- Name: profissao_codprofissao_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('profissao_codprofissao_seq', 20, true);


--
-- TOC entry 1585 (class 1259 OID 17884)
-- Dependencies: 3 1586
-- Name: profissional_codprofissional_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE profissional_codprofissional_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    MINVALUE 0
    CACHE 1;


ALTER TABLE public.profissional_codprofissional_seq OWNER TO postgres;

--
-- TOC entry 1979 (class 0 OID 0)
-- Dependencies: 1585
-- Name: profissional_codprofissional_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE profissional_codprofissional_seq OWNED BY profissional.codprofissional;


--
-- TOC entry 1980 (class 0 OID 0)
-- Dependencies: 1585
-- Name: profissional_codprofissional_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('profissional_codprofissional_seq', 4, true);


--
-- TOC entry 1591 (class 1259 OID 17917)
-- Dependencies: 1920 1921 3
-- Name: solicitacao; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE solicitacao (
    codsolicitacao bigint NOT NULL,
    clientecodcliente bigint NOT NULL,
    profissaocodprofissao integer NOT NULL,
    qtde integer,
    datasolicitacao date,
    dataatendimento date,
    CONSTRAINT valida_atendimento CHECK ((dataatendimento > datasolicitacao)),
    CONSTRAINT valida_quantidade CHECK ((qtde > 0))
);


ALTER TABLE public.solicitacao OWNER TO postgres;

--
-- TOC entry 1590 (class 1259 OID 17915)
-- Dependencies: 3 1591
-- Name: solicitacao_codsolicitacao_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE solicitacao_codsolicitacao_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    MINVALUE 0
    CACHE 1;


ALTER TABLE public.solicitacao_codsolicitacao_seq OWNER TO postgres;

--
-- TOC entry 1981 (class 0 OID 0)
-- Dependencies: 1590
-- Name: solicitacao_codsolicitacao_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE solicitacao_codsolicitacao_seq OWNED BY solicitacao.codsolicitacao;


--
-- TOC entry 1982 (class 0 OID 0)
-- Dependencies: 1590
-- Name: solicitacao_codsolicitacao_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('solicitacao_codsolicitacao_seq', 6, true);


--
-- TOC entry 1611 (class 1259 OID 18700)
-- Dependencies: 1925 1926 1927 329 3 326
-- Name: users; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE users (
    id integer NOT NULL,
    nome dm_nome,
    tipo character(1),
    codigo integer,
    email dm_local,
    ativo boolean DEFAULT true,
    login character varying(10),
    senha character varying(15) DEFAULT ''::character varying,
    CONSTRAINT users_tipo_check CHECK ((((tipo = 'C'::bpchar) OR (tipo = 'P'::bpchar)) OR (tipo = 'O'::bpchar)))
);


ALTER TABLE public.users OWNER TO postgres;

--
-- TOC entry 1610 (class 1259 OID 18698)
-- Dependencies: 3 1611
-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE users_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    MINVALUE 0
    CACHE 1;


ALTER TABLE public.users_id_seq OWNER TO postgres;

--
-- TOC entry 1983 (class 0 OID 0)
-- Dependencies: 1610
-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE users_id_seq OWNED BY users.id;


--
-- TOC entry 1984 (class 0 OID 0)
-- Dependencies: 1610
-- Name: users_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('users_id_seq', 2, true);


--
-- TOC entry 1593 (class 1259 OID 17964)
-- Dependencies: 1698 3 326 326
-- Name: vw_area_profissao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_area_profissao AS
    SELECT a.codarea, a.descricao AS area, p.codprofissao, p.descricao AS profissao FROM (area a JOIN profissao p ON ((a.codarea = p.areacodarea)));


ALTER TABLE public.vw_area_profissao OWNER TO postgres;

--
-- TOC entry 1595 (class 1259 OID 17977)
-- Dependencies: 1700 3 326 326
-- Name: vw_cliente_profissional; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_cliente_profissional AS
    SELECT s.codsolicitacao, c.codcliente, c.nomecli AS nomecliente, p.codprofissional, p.nome AS nomeprofissional, cp.nivel, cp.aceito FROM (((clientesprofissional cp JOIN profissional p ON ((p.codprofissional = cp.profissionalcodprofissional))) JOIN solicitacao s ON ((s.codsolicitacao = cp.solicitacaocodsolicitacao))) JOIN cliente c ON ((c.codcliente = s.clientecodcliente)));


ALTER TABLE public.vw_cliente_profissional OWNER TO postgres;

--
-- TOC entry 1596 (class 1259 OID 17982)
-- Dependencies: 1701 326 326 3 326 326
-- Name: vw_cliente_profissional_geral; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_cliente_profissional_geral AS
    SELECT s.codsolicitacao, c.codcliente, c.nomecli AS nomecliente, p.codprofissional, p.nome AS nomeprofissional, prof.codprofissao, prof.descricao AS profissao, a.codarea, a.descricao AS area, cp.nivel, cp.aceito FROM (((((clientesprofissional cp JOIN profissional p ON ((p.codprofissional = cp.profissionalcodprofissional))) JOIN solicitacao s ON ((s.codsolicitacao = cp.solicitacaocodsolicitacao))) JOIN cliente c ON ((c.codcliente = s.clientecodcliente))) JOIN profissao prof ON ((prof.codprofissao = s.profissaocodprofissao))) JOIN area a ON ((a.codarea = prof.areacodarea)));


ALTER TABLE public.vw_cliente_profissional_geral OWNER TO postgres;

--
-- TOC entry 1603 (class 1259 OID 18643)
-- Dependencies: 1707 329 3 332 330 329 329 328 328 327 326 331
-- Name: vw_profissionais_disponiveis; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_profissionais_disponiveis AS
    SELECT profissional.codprofissional, profissional.cpf, profissional.nome, profissional.fantasia, profissional.sexo, profissional.fonefixo, profissional.fonecel, profissional.endereco, profissional.bairro, profissional.cidade, profissional.uf, profissional.rg_num, profissional.rg_org_exp, profissional.rg_data_exp, profissional.carteiraprofissional, profissional.cnh_registro, profissional.cnh_cat, profissional.cnh_vencto, profissional.datanascimento, profissional.alocado FROM profissional WHERE (profissional.alocado = false);


ALTER TABLE public.vw_profissionais_disponiveis OWNER TO postgres;

--
-- TOC entry 1604 (class 1259 OID 18652)
-- Dependencies: 1708 326 3 326
-- Name: vw_solicitacoes; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_solicitacoes AS
    SELECT solicitacao.codsolicitacao, cliente.codcliente, cliente.nomecli AS cliente, solicitacao.qtde AS quantidade, profissao.codprofissao, profissao.descricao AS profissao, solicitacao.datasolicitacao, solicitacao.dataatendimento FROM ((solicitacao JOIN profissao ON ((profissao.codprofissao = solicitacao.profissaocodprofissao))) JOIN cliente ON ((cliente.codcliente = solicitacao.clientecodcliente)));


ALTER TABLE public.vw_solicitacoes OWNER TO postgres;

--
-- TOC entry 1606 (class 1259 OID 18664)
-- Dependencies: 1709 326 3
-- Name: vw_qtd_profissao_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_qtd_profissao_solicitacao AS
    SELECT vw_solicitacoes.codprofissao, vw_solicitacoes.profissao, count(vw_solicitacoes.codprofissao) AS quantia FROM vw_solicitacoes GROUP BY vw_solicitacoes.codprofissao, vw_solicitacoes.profissao;


ALTER TABLE public.vw_qtd_profissao_solicitacao OWNER TO postgres;

--
-- TOC entry 1597 (class 1259 OID 17995)
-- Dependencies: 1702 326 3
-- Name: vw_qtd_profissional_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_qtd_profissional_solicitacao AS
    SELECT vw_cliente_profissional.codprofissional, vw_cliente_profissional.nomeprofissional, count(vw_cliente_profissional.codprofissional) AS quantia FROM vw_cliente_profissional GROUP BY vw_cliente_profissional.codprofissional, vw_cliente_profissional.nomeprofissional;


ALTER TABLE public.vw_qtd_profissional_solicitacao OWNER TO postgres;

--
-- TOC entry 1607 (class 1259 OID 18668)
-- Dependencies: 1710 326 3
-- Name: vw_soma_profissao_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_soma_profissao_solicitacao AS
    SELECT vw_solicitacoes.codprofissao, vw_solicitacoes.profissao, sum(vw_solicitacoes.codprofissao) AS quantia FROM vw_solicitacoes GROUP BY vw_solicitacoes.codprofissao, vw_solicitacoes.profissao;


ALTER TABLE public.vw_soma_profissao_solicitacao OWNER TO postgres;

--
-- TOC entry 1598 (class 1259 OID 17999)
-- Dependencies: 1703 326 3
-- Name: vwr_area_quantidade; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vwr_area_quantidade AS
    SELECT vw_area_profissao.area, count(vw_area_profissao.profissao) AS quantidade FROM vw_area_profissao GROUP BY vw_area_profissao.area ORDER BY count(vw_area_profissao.profissao) DESC, vw_area_profissao.area;


ALTER TABLE public.vwr_area_quantidade OWNER TO postgres;

--
-- TOC entry 1601 (class 1259 OID 18015)
-- Dependencies: 1706 3 326
-- Name: vwr_qtd_area_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vwr_qtd_area_solicitacao AS
    SELECT vw_cliente_profissional_geral.area, count(vw_cliente_profissional_geral.codarea) AS quantia FROM vw_cliente_profissional_geral GROUP BY vw_cliente_profissional_geral.area ORDER BY count(vw_cliente_profissional_geral.codarea) DESC, vw_cliente_profissional_geral.area;


ALTER TABLE public.vwr_qtd_area_solicitacao OWNER TO postgres;

--
-- TOC entry 1608 (class 1259 OID 18672)
-- Dependencies: 1711 326 3
-- Name: vwr_qtd_profissao_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vwr_qtd_profissao_solicitacao AS
    SELECT vw_qtd_profissao_solicitacao.profissao, vw_qtd_profissao_solicitacao.quantia FROM vw_qtd_profissao_solicitacao ORDER BY vw_qtd_profissao_solicitacao.quantia DESC, vw_qtd_profissao_solicitacao.profissao;


ALTER TABLE public.vwr_qtd_profissao_solicitacao OWNER TO postgres;

--
-- TOC entry 1600 (class 1259 OID 18011)
-- Dependencies: 1705 326 3
-- Name: vwr_qtd_profissional_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vwr_qtd_profissional_solicitacao AS
    SELECT vw_qtd_profissional_solicitacao.nomeprofissional, vw_qtd_profissional_solicitacao.quantia FROM vw_qtd_profissional_solicitacao ORDER BY vw_qtd_profissional_solicitacao.quantia DESC, vw_qtd_profissional_solicitacao.nomeprofissional;


ALTER TABLE public.vwr_qtd_profissional_solicitacao OWNER TO postgres;

--
-- TOC entry 1609 (class 1259 OID 18676)
-- Dependencies: 1712 3 326
-- Name: vwr_soma_cliente_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vwr_soma_cliente_solicitacao AS
    SELECT vw_solicitacoes.cliente, sum(vw_solicitacoes.quantidade) AS quantidade FROM vw_solicitacoes GROUP BY vw_solicitacoes.cliente ORDER BY sum(vw_solicitacoes.quantidade) DESC, vw_solicitacoes.cliente;


ALTER TABLE public.vwr_soma_cliente_solicitacao OWNER TO postgres;

--
-- TOC entry 1904 (class 2604 OID 17867)
-- Dependencies: 1582 1581 1582
-- Name: codarea; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE area ALTER COLUMN codarea SET DEFAULT nextval('area_codarea_seq'::regclass);


--
-- TOC entry 1915 (class 2604 OID 17907)
-- Dependencies: 1589 1588 1589
-- Name: codcliente; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE cliente ALTER COLUMN codcliente SET DEFAULT nextval('cliente_codcliente_seq'::regclass);


--
-- TOC entry 1905 (class 2604 OID 17878)
-- Dependencies: 1584 1583 1584
-- Name: codprofissao; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE profissao ALTER COLUMN codprofissao SET DEFAULT nextval('profissao_codprofissao_seq'::regclass);


--
-- TOC entry 1906 (class 2604 OID 17889)
-- Dependencies: 1586 1585 1586
-- Name: codprofissional; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE profissional ALTER COLUMN codprofissional SET DEFAULT nextval('profissional_codprofissional_seq'::regclass);


--
-- TOC entry 1919 (class 2604 OID 17920)
-- Dependencies: 1590 1591 1591
-- Name: codsolicitacao; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE solicitacao ALTER COLUMN codsolicitacao SET DEFAULT nextval('solicitacao_codsolicitacao_seq'::regclass);


--
-- TOC entry 1924 (class 2604 OID 18703)
-- Dependencies: 1610 1611 1611
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE users ALTER COLUMN id SET DEFAULT nextval('users_id_seq'::regclass);


--
-- TOC entry 1960 (class 0 OID 17864)
-- Dependencies: 1582
-- Data for Name: area; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO area (codarea, descricao) VALUES (1, 'Informática');
INSERT INTO area (codarea, descricao) VALUES (2, 'Biológica');
INSERT INTO area (codarea, descricao) VALUES (3, 'Pornográfica');


--
-- TOC entry 1964 (class 0 OID 17904)
-- Dependencies: 1589
-- Data for Name: cliente; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO cliente (codcliente, nomecli, fantasia, endereco, bairro, cidade, uf, fonefixo, fonefax, fonecelular, email, homepage, tipo, datanasctoabertura, cpfcnpj, rgie) VALUES (1, 'Eidy', 'nenhuma', 'Endereco?', 'Bairro?', 'Londrina', 'PR', '(99) 9999-9999', '(88) 8888-8888', '(77) 7777-7777', 'eidy@microsoft.com', 'www.google.com.br', '?', '2011-06-23', '012345678901234567', '012345678901234567');
INSERT INTO cliente (codcliente, nomecli, fantasia, endereco, bairro, cidade, uf, fonefixo, fonefax, fonecelular, email, homepage, tipo, datanasctoabertura, cpfcnpj, rgie) VALUES (2, 'Shishido', 'nenhuma', 'desconhecido', 'nao se sabe', 'nao se faz ideia', 'PR', '(22) 2222-2222', '(11) 1111-1111', '(00) 0000-0000', 'eidy@microsoft.com', 'www.google.com.br', '?', '2011-06-23', '222222222222222222', '222222222222222222');


--
-- TOC entry 1966 (class 0 OID 17923)
-- Dependencies: 1592
-- Data for Name: clientesprofissional; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO clientesprofissional (solicitacaocodsolicitacao, profissionalcodprofissional, aceito, nivel) VALUES (4, 2, true, 4);
INSERT INTO clientesprofissional (solicitacaocodsolicitacao, profissionalcodprofissional, aceito, nivel) VALUES (5, 3, true, 3);
INSERT INTO clientesprofissional (solicitacaocodsolicitacao, profissionalcodprofissional, aceito, nivel) VALUES (6, 4, true, 4);
INSERT INTO clientesprofissional (solicitacaocodsolicitacao, profissionalcodprofissional, aceito, nivel) VALUES (6, 1, true, 5);


--
-- TOC entry 1963 (class 0 OID 17897)
-- Dependencies: 1587
-- Data for Name: especializacao; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (1, 1, '2009-01-01', 5);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (1, 2, '2009-01-01', 3);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (1, 8, '2009-01-01', 1);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (2, 4, '2009-01-01', 3);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (2, 6, '2009-01-01', 3);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (2, 1, '2009-01-01', 3);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (3, 5, '2009-01-01', 4);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (3, 3, '2009-01-01', 5);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (4, 7, '2009-01-01', 4);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (4, 8, '2009-01-01', 4);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (4, 4, '2009-01-01', 1);


--
-- TOC entry 1961 (class 0 OID 17875)
-- Dependencies: 1584
-- Data for Name: profissao; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (1, 'Analista De Sistemas', 1);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (2, 'Programador', 1);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (3, 'Cara Das Rede', 1);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (4, 'Médico', 2);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (5, 'Dentista', 2);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (6, 'Cirurgião', 2);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (7, 'Cafetão', 3);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (8, 'Ator', 3);


--
-- TOC entry 1962 (class 0 OID 17886)
-- Dependencies: 1586
-- Data for Name: profissional; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (1, '111.111.111-11', 'Diogo Hideki Matsumoto', 'nenhuma', 'm', '(99) 9999-9999', '(11) 1234-1234', 'Rua Qualquer', 'Centro', 'Ibipa', 'PR', '1.111.111-11', 'naosei', '1995-09-09', '01234567890123', '012345678901', '0123456789', '1998-02-25', '1991-07-26', false);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (2, '222.222.222-22', 'Aline Yukita', 'nenhuma', 'f', '(22) 2222-2222', '(22) 2345-2345', 'Rua Qualquer', 'Centro', 'Assaí', 'PR', '2.222.222-22', 'naosei', '1997-05-05', '22222222222222', '222222222222', '2222222222', '1999-10-22', '1991-02-06', false);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (3, '333.333.333-33', 'Marcio Edsao', 'nenhuma', 'm', '(33) 3333-3333', '(33) 3456-3456', 'Rua Qualquer', 'Centro', 'Maracai', 'PR', '3.333.333-33', 'naosei', '1957-06-24', '33333333333333', '333333333333', '3333333333', '1959-12-11', '1955-01-01', false);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (4, '444.444.444-44', 'Pablo Paulo da Silva', 'nenhuma', 'm', '(44) 4444-4444', '(44) 4567-4567', 'Rua Qualquer', 'Centro', 'Cornélio', 'PR', '4.444.444-44', 'naosei', '1992-08-08', '44444444444444', '444444444444', '4444444444', '1995-01-25', '1989-03-09', false);


--
-- TOC entry 1965 (class 0 OID 17917)
-- Dependencies: 1591
-- Data for Name: solicitacao; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO solicitacao (codsolicitacao, clientecodcliente, profissaocodprofissao, qtde, datasolicitacao, dataatendimento) VALUES (1, 1, 1, 2, '2011-06-08', '2011-06-11');
INSERT INTO solicitacao (codsolicitacao, clientecodcliente, profissaocodprofissao, qtde, datasolicitacao, dataatendimento) VALUES (2, 1, 2, 4, '2011-06-18', '2011-06-19');
INSERT INTO solicitacao (codsolicitacao, clientecodcliente, profissaocodprofissao, qtde, datasolicitacao, dataatendimento) VALUES (3, 1, 3, 2, '2011-06-18', '2011-06-19');
INSERT INTO solicitacao (codsolicitacao, clientecodcliente, profissaocodprofissao, qtde, datasolicitacao, dataatendimento) VALUES (4, 2, 4, 1, '2011-06-15', '2011-06-21');
INSERT INTO solicitacao (codsolicitacao, clientecodcliente, profissaocodprofissao, qtde, datasolicitacao, dataatendimento) VALUES (5, 2, 5, 1, '2011-06-15', '2011-06-22');
INSERT INTO solicitacao (codsolicitacao, clientecodcliente, profissaocodprofissao, qtde, datasolicitacao, dataatendimento) VALUES (6, 2, 8, 2, '2011-06-21', '2011-06-23');


--
-- TOC entry 1967 (class 0 OID 18700)
-- Dependencies: 1611
-- Data for Name: users; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO users (id, nome, tipo, codigo, email, ativo, login, senha) VALUES (1, 'Diogo', 'P', 1, 'aaa', true, 'diogo', 'senha');
INSERT INTO users (id, nome, tipo, codigo, email, ativo, login, senha) VALUES (2, 'Eidy', 'C', 1, 'eid@cliente', true, 'eid', 'eid');


--
-- TOC entry 1929 (class 2606 OID 17872)
-- Dependencies: 1582 1582
-- Name: area_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY area
    ADD CONSTRAINT area_pkey PRIMARY KEY (codarea);


--
-- TOC entry 1941 (class 2606 OID 17914)
-- Dependencies: 1589 1589
-- Name: cliente_cpfcnpj_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY cliente
    ADD CONSTRAINT cliente_cpfcnpj_key UNIQUE (cpfcnpj);


--
-- TOC entry 1943 (class 2606 OID 17912)
-- Dependencies: 1589 1589
-- Name: cliente_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY cliente
    ADD CONSTRAINT cliente_pkey PRIMARY KEY (codcliente);


--
-- TOC entry 1947 (class 2606 OID 17928)
-- Dependencies: 1592 1592 1592
-- Name: clientesprofissional_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY clientesprofissional
    ADD CONSTRAINT clientesprofissional_pkey PRIMARY KEY (solicitacaocodsolicitacao, profissionalcodprofissional);


--
-- TOC entry 1939 (class 2606 OID 17901)
-- Dependencies: 1587 1587 1587
-- Name: especializacao_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY especializacao
    ADD CONSTRAINT especializacao_pkey PRIMARY KEY (profissionalcodprofissional, profissaocodprofissao);


--
-- TOC entry 1931 (class 2606 OID 17883)
-- Dependencies: 1584 1584
-- Name: profissao_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY profissao
    ADD CONSTRAINT profissao_pkey PRIMARY KEY (codprofissao);


--
-- TOC entry 1935 (class 2606 OID 17896)
-- Dependencies: 1586 1586
-- Name: profissional_cpf_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY profissional
    ADD CONSTRAINT profissional_cpf_key UNIQUE (cpf);


--
-- TOC entry 1937 (class 2606 OID 17894)
-- Dependencies: 1586 1586
-- Name: profissional_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY profissional
    ADD CONSTRAINT profissional_pkey PRIMARY KEY (codprofissional);


--
-- TOC entry 1945 (class 2606 OID 17922)
-- Dependencies: 1591 1591
-- Name: solicitacao_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY solicitacao
    ADD CONSTRAINT solicitacao_pkey PRIMARY KEY (codsolicitacao);


--
-- TOC entry 1933 (class 2606 OID 19230)
-- Dependencies: 1584 1584
-- Name: unq_profissao; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY profissao
    ADD CONSTRAINT unq_profissao UNIQUE (descricao);


--
-- TOC entry 1949 (class 2606 OID 18711)
-- Dependencies: 1611 1611
-- Name: users_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);


--
-- TOC entry 1958 (class 2620 OID 18607)
-- Dependencies: 1592 21
-- Name: geraatualizacaonivelprofissional; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER geraatualizacaonivelprofissional
    BEFORE UPDATE ON clientesprofissional
    FOR EACH ROW
    EXECUTE PROCEDURE atualizanivelprofissional();


--
-- TOC entry 1959 (class 2620 OID 19234)
-- Dependencies: 1592 38
-- Name: tg_atualizaalocacaoprofissional; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER tg_atualizaalocacaoprofissional
    AFTER UPDATE ON clientesprofissional
    FOR EACH ROW
    EXECUTE PROCEDURE atualizaalocacaoprofissional();


--
-- TOC entry 1957 (class 2620 OID 19232)
-- Dependencies: 1584 42
-- Name: tg_verifica_nome; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER tg_verifica_nome
    BEFORE INSERT OR UPDATE ON profissao
    FOR EACH ROW
    EXECUTE PROCEDURE fn_verifica_nome();


--
-- TOC entry 1955 (class 2606 OID 17954)
-- Dependencies: 1591 1592 1944
-- Name: fkclientespr183846; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY clientesprofissional
    ADD CONSTRAINT fkclientespr183846 FOREIGN KEY (solicitacaocodsolicitacao) REFERENCES solicitacao(codsolicitacao);


--
-- TOC entry 1956 (class 2606 OID 17959)
-- Dependencies: 1586 1592 1936
-- Name: fkclientespr72559; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY clientesprofissional
    ADD CONSTRAINT fkclientespr72559 FOREIGN KEY (profissionalcodprofissional) REFERENCES profissional(codprofissional);


--
-- TOC entry 1951 (class 2606 OID 17934)
-- Dependencies: 1936 1586 1587
-- Name: fkespecializ744318; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY especializacao
    ADD CONSTRAINT fkespecializ744318 FOREIGN KEY (profissionalcodprofissional) REFERENCES profissional(codprofissional);


--
-- TOC entry 1952 (class 2606 OID 17939)
-- Dependencies: 1587 1930 1584
-- Name: fkespecializ926790; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY especializacao
    ADD CONSTRAINT fkespecializ926790 FOREIGN KEY (profissaocodprofissao) REFERENCES profissao(codprofissao);


--
-- TOC entry 1950 (class 2606 OID 17929)
-- Dependencies: 1584 1582 1928
-- Name: fkprofissao66504; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY profissao
    ADD CONSTRAINT fkprofissao66504 FOREIGN KEY (areacodarea) REFERENCES area(codarea);


--
-- TOC entry 1954 (class 2606 OID 17949)
-- Dependencies: 1584 1591 1930
-- Name: fksolicitaca640088; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY solicitacao
    ADD CONSTRAINT fksolicitaca640088 FOREIGN KEY (profissaocodprofissao) REFERENCES profissao(codprofissao);


--
-- TOC entry 1953 (class 2606 OID 17944)
-- Dependencies: 1942 1589 1591
-- Name: fksolicitaca741811; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY solicitacao
    ADD CONSTRAINT fksolicitaca741811 FOREIGN KEY (clientecodcliente) REFERENCES cliente(codcliente);


--
-- TOC entry 1972 (class 0 OID 0)
-- Dependencies: 3
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


-- Completed on 2011-06-23 20:47:49 BRT

--
-- PostgreSQL database dump complete
--

